package com.bw.dao;

import com.bw.bean.Sugar;
import com.bw.bean.Sugar1;
import com.bw.bean.Sugar2;
import com.bw.bean.Sugar3;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface SugarDao {
    @Select("SELECT b.name,o.final_total_amount value FROM order_info o\n" +
            "INNER JOIN base_province b ON o.province_id=b.id\n" +
            "GROUP BY b.NAME")
    List<Sugar> get1();


    @Select("SELECT t.name,sum(i.final_total_amount) value FROM order_info i \n" +
            "INNER JOIN order_detail o  ON i.id=o.order_id\n" +
            "INNER JOIN sku_info s ON o.sku_id=s.id \n" +
            "INNER JOIN base_trademark t ON s.tm_id=t.id \n" +
            "GROUP BY t.name")
    List<Sugar> get2();

    @Select("SELECT b1.name,sum(o.final_total_amount) value FROM order_info o\n" +
            "INNER JOIN order_detail d ON o.id=d.order_id \n" +
            "INNER JOIN sku_info i ON d.sku_id=i.id \n" +
            "INNER JOIN base_category3 b3 ON i.category3_id=b3.id \n" +
            "INNER JOIN base_category2 b2 ON b3.category2_id=b2.id \n" +
            "INNER JOIN base_category1 b1 ON b2.category1_id=b1.id\n" +
            "GROUP BY b1.name")
    List<Sugar> get3();

    @Select("SELECT b2.name,sum(o.final_total_amount) value FROM order_info o\n" +
            "INNER JOIN order_detail d ON o.id=d.order_id \n" +
            "INNER JOIN sku_info i ON d.sku_id=i.id \n" +
            "INNER JOIN base_category3 b3 ON i.category3_id=b3.id \n" +
            "INNER JOIN base_category2 b2 ON b3.category2_id=b2.id \n" +
            "GROUP BY b2.name")
    List<Sugar> get4();

    @Select("SELECT * FROM\n" +
            "(SELECT count(*) value1 FROM user_info WHERE TIMESTAMPDIFF(DAY,create_time,NOW())<=14)a,\n" +
            "(SELECT count(*) value2 FROM user_info WHERE TIMESTAMPDIFF(DAY,create_time,NOW())>14)b")
    List<Sugar1> get5();


    @Select("SELECT  CONCAT(SUBSTR(birthday,3,1),'0后') name,sum(o.final_total_amount) value FROM order_info o\n" +
            "INNER JOIN user_info u ON o.user_id=u.id \n" +
            "INNER JOIN order_detail d ON o.id=d.order_id \n" +
            "INNER JOIN sku_info s ON d.sku_id=s.id \n" +
            "INNER JOIN base_trademark b ON s.tm_id=b.id\n" +
            "GROUP BY  CONCAT(SUBSTR(birthday,3,1),'0后')")
    List<Sugar> get6();

    @Select("SELECT yys name,sum(final_total_amount) value FROM order_info WHERE yys is not  null  GROUP BY yys\n")
    List<Sugar> get7();

    @Select("SELECT  b.name,if(u.gender='F','男','女') sex,sum(o.final_total_amount) value FROM order_info o\n" +
            "INNER JOIN user_info u ON o.user_id=u.id \n" +
            "INNER JOIN order_detail d ON o.id=d.order_id \n" +
            "INNER JOIN sku_info s ON d.sku_id=s.id \n" +
            "INNER JOIN base_trademark b ON s.tm_id=b.id\n" +
            "GROUP BY b.name,sex")
    List<Sugar2> get8();

    @Select("call qwer")
    Double get9();

    @Select("SELECT p.spu_name name,sum(i.final_total_amount) value FROM  order_info i\n" +
            "INNER JOIN order_detail o ON i.id=o.order_id \n" +
            "INNER JOIN sku_info k ON o.sku_id=k.id \n" +
            "INNER JOIN spu_info p ON k.spu_id=p.id\n" +
            "GROUP BY p.spu_name")
    List<Sugar3> get0();
}
